package com.seeyon.apps.gkcwplugin.manager.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.seeyon.apps.gkcwplugin.dao.DealDataDao;
import com.seeyon.apps.gkcwplugin.manager.DealSubjectManager;
import com.seeyon.apps.gkcwplugin.po.DeptToSubject;
import com.seeyon.apps.gkcwplugin.po.SubjectPO;
import com.seeyon.ctp.common.AppContext;

//手动处理部门对应科目字典数据类
public class DealSubjectManagerImpl implements DealSubjectManager{
	private static final Log logger=LogFactory.getLog(DealSubjectManagerImpl.class);
	private DealDataDao dealDataDao=(DealDataDao)AppContext.getBean("dealDataDao");
	@Override
	public void dealSubject() {
		Workbook workbook = null;
        Row row = null;
        //获取Excel文档
        workbook = getWorkbook("D:\\对应科目\\对应科目.xlsx");
        //获取Excel文档的第一个sheet页
        Sheet sheet = workbook.getSheetAt(0);
        //获取文档中已保存数据的行数
        int rowNum = sheet.getPhysicalNumberOfRows();
        logger.info("----------------行数："+rowNum);
        //获取第一行
        row = sheet.getRow(0);
        //获取当前行已保存数据的最大列数
        int colnum = row.getPhysicalNumberOfCells();
        logger.info("----------------列数："+colnum);
        String kmbh="";
        SubjectPO yjpo=null;
   	 	List<SubjectPO> subjectList=null;
   	 	int count=0;
   	 	List<DeptToSubject> deptSubjectList=null;
        for (int i = 1; i < rowNum; i++) {
        	
        	deptSubjectList=new ArrayList<DeptToSubject>();
        	logger.info("---------------row:"+i);
            row = sheet.getRow(i);
            if (null != row){
            	
            	Cell dwbhCell=row.getCell(0);
            	String dwbh=getValueFromCell(dwbhCell).toString();
            	if ("".equals(dwbh)||dwbh==null) {//如果单位编号为空则停止循环
					break;
				}
            	Cell dwmcCell=row.getCell(1);
            	Cell bmbhCell=row.getCell(2);
            	Cell bmmcCell=row.getCell(3);
            	Cell bhCell = row.getCell(4);
            	if (!"".equals(kmbh)&&getValueFromCell(bhCell).toString().equals(kmbh)) {//如果当前行的所属科目编号和上一行的相同则不需要查询科目表
            		kmbh=getValueFromCell(bhCell).toString();
                	yjpo=getYjpo(getValueFromCell(bhCell).toString());
                    if (subjectList!=null&&subjectList.size()>0) {
                    	 logger.info("-------------------不是第一次处理，list条数："+subjectList.size());
                    	logger.info("-------------科目编号："+subjectList.get(0).getZWKMZD_KMBH()+",科目名称："+subjectList.get(0).getZWKMZD_KMMC());
                    	for(int n=0;n<subjectList.size();n++) {
                    		SubjectPO po=subjectList.get(n);
                    		
                        	DeptToSubject deptToSubject=new DeptToSubject();
                        	deptToSubject.setIdIfNew();
                        	deptToSubject.setDWBH(getValueFromCell(dwbhCell).toString());
                        	deptToSubject.setDWMC(getValueFromCell(dwmcCell).toString());
                        	deptToSubject.setBMBH(getValueFromCell(bmbhCell).toString());
                        	deptToSubject.setBMMC(getValueFromCell(bmmcCell).toString());
                        	deptToSubject.setKMNM(po.getZWKMZD_ID());
                        	deptToSubject.setKMID(String.valueOf(po.getId()));
                        	deptToSubject.setKMBH(po.getZWKMZD_KMBH());
                        	deptToSubject.setKMMC(po.getZWKMZD_KMMC());
                        	deptToSubject.setSX(po.getZWKMZD_SX());
                        	if (yjpo!=null) {
                        		deptToSubject.setDYKMBH(yjpo.getZWKMZD_KMBH());
                            	deptToSubject.setDYKMMC(yjpo.getZWKMZD_KMMC());
    						}
                        	
                        	deptToSubject.setKMQC(po.getZWKMZD_KMQC());
                        	deptToSubject.setMX(po.getZWKMZD_MX());
                        	deptToSubject.setXZ(po.getZWKMZD_XZ());
                        	deptToSubject.setKMLX(po.getZWKMZD_KMLX());
                        	deptToSubject.setZWKMZD_TYBZ(po.getZWKMZD_TYBZ());
                        	deptToSubject.setCREATETIME(po.getCREATETIME());
                        	deptToSubject.setLASTMODIFIEDTIME(po.getLASTMODIFIEDTIME());
                        	deptSubjectList.add(deptToSubject);
                        	count++;
                        }
                    	dealDataDao.saveDeptSubject(deptSubjectList);
                    }
				}else {
					
					kmbh=getValueFromCell(bhCell).toString();
	            	yjpo=getYjpo(getValueFromCell(bhCell).toString());
	           	 	subjectList=dealDataDao.findSubjectByBh(getValueFromCell(bhCell).toString());
	           	 	logger.info("-------------------第一次处理，list条数："+subjectList.size());
	           	 	if (subjectList!=null&&subjectList.size()>0) {
	                	for(int n=0;n<subjectList.size();n++) {
	                		logger.info("------------------n:"+n);
	                		SubjectPO po=subjectList.get(n);
	                		
	                    	DeptToSubject deptToSubject=new DeptToSubject();
	                    	deptToSubject.setIdIfNew();
	                    	deptToSubject.setDWBH(getValueFromCell(dwbhCell).toString());
	                    	deptToSubject.setDWMC(getValueFromCell(dwmcCell).toString());
	                    	deptToSubject.setBMBH(getValueFromCell(bmbhCell).toString());
	                    	deptToSubject.setBMMC(getValueFromCell(bmmcCell).toString());
	                    	deptToSubject.setKMNM(po.getZWKMZD_ID());
	                    	deptToSubject.setKMID(String.valueOf(po.getId()));
	                    	deptToSubject.setKMBH(po.getZWKMZD_KMBH());
	                    	deptToSubject.setKMMC(po.getZWKMZD_KMMC());
	                    	deptToSubject.setSX(po.getZWKMZD_SX());
	                    	if (yjpo!=null) {
	                    		deptToSubject.setDYKMBH(yjpo.getZWKMZD_KMBH());
	                        	deptToSubject.setDYKMMC(yjpo.getZWKMZD_KMMC());
							}
	                    	
	                    	deptToSubject.setKMQC(po.getZWKMZD_KMQC());
	                    	deptToSubject.setMX(po.getZWKMZD_MX());
	                    	deptToSubject.setXZ(po.getZWKMZD_XZ());
	                    	deptToSubject.setKMLX(po.getZWKMZD_KMLX());
	                    	deptToSubject.setZWKMZD_TYBZ(po.getZWKMZD_TYBZ());
	                    	deptToSubject.setCREATETIME(po.getCREATETIME());
	                    	deptToSubject.setLASTMODIFIEDTIME(po.getLASTMODIFIEDTIME());
	                    	deptSubjectList.add(deptToSubject);
	                    	count++;
	                    }
	                	dealDataDao.saveDeptSubject(deptSubjectList);
	                }
				}
            	
                
                logger.info("----------------部门编号单元格："+getValueFromCell(bmbhCell));

//                for (int j = 0; j < colnum; j++) {
//                	
//                }
            }
        }
        logger.info("-------------------执行总记录数："+count);
	}
	
	 private static Workbook getWorkbook(String fileName) {//根据后缀获取Excel表格
	        Workbook workbook = null;
	        String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
	        InputStream in = null;
	        try {
	            in = new FileInputStream(fileName);
	            if ("xls".equals(suffix))
	            {
	                workbook = new HSSFWorkbook(in);
	            }
	            else if ("xlsx".equals(suffix))
	            {
	                workbook = new XSSFWorkbook(in);
	            }
	        } catch (FileNotFoundException e) {
	            e.printStackTrace();
	        } catch (IOException e) {
	            e.printStackTrace();
	        }
	        return workbook;
	    }
	    
	    private static Object getValueFromCell(Cell cell) {//获取单元格的值
	        Object value = null;
	        if (null == cell)
	        {
	            return "";
	        }
	        value = cell.getRichStringCellValue().getString();
	        //判断cell类型
//	        cell.getRichStringCellValue().toString();
//	        switch(cell.getCellType()){
//	        case Cell.CELL_TYPE_NUMERIC:{
//	            value = String.valueOf(cell.getNumericCellValue());
//	            break;
//	        }
//	        case Cell.CELL_TYPE_FORMULA:{
//	            //判断cell是否为日期格式
//	            if(DateUtil.isCellDateFormatted(cell)){
//	                //转换为日期格式YYYY-mm-dd
//	                value = cell.getDateCellValue();
//	            }else{
//	                //数字
//	                value = String.valueOf(cell.getNumericCellValue());
//	            }
//	            break;
//	        }
//	        case Cell.CELL_TYPE_STRING:{
//	            value = cell.getRichStringCellValue().getString();
//	            break;
//	        }
//	        default:
//	            value = "";
//	        }
	        return value;
	    }
	    public SubjectPO getYjpo(String kmbh) {
	    	SubjectPO yjPO=dealDataDao.findSubjectPo(kmbh);
        	return yjPO;
	    }
}
